import traceback
import math
from sqlalchemy.sql import text
from flask import g, has_app_context
from framework import db
from framework.constant.Testing import Testing


class DbUtil:
    """
    数据库操作类
    """

    def __init__(self):
        pass

    def __get_engine(self, target_db=None, db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        if all([db_username, db_passwd, db_host, db_port, db_name]):
            return db.create_engine(f"mysql://{db_username}:{db_passwd}@{db_host}:{db_port}/{db_name}")
        else:
            # if target_db is None:
            #     target_db = self.__if_post_for_unit_testing()

            if target_db:
                return db.get_engine(bind=target_db)

            return db.get_engine()

    def __if_post_for_unit_testing(self):
        if has_app_context():
            params = dict(g.request_data)
            if params.get(Testing.PARAM_NAME) == Testing.PARAM_VALUE:
                return Testing.TEST_DB

        return None

    def query(self, statement_sql, args: dict = None, default_result=None, target_db=None,
              db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        result = default_result

        try:
            engine = self.__get_engine(target_db, db_username, db_passwd, db_host, db_port, db_name)

            if args is None:
                result_proxy = engine.execute(statement_sql)
            else:
                if isinstance(statement_sql, str):
                    statement_sql = text(statement_sql)

                result_proxy = engine.execute(statement_sql, **args)

            if result_proxy.returns_rows:
                result, datum = [], {}
                for row_proxy in result_proxy:
                    for column, value in row_proxy.items():
                        datum = {**datum, **{column: value}}
                    result.append(datum)
            else:
                return None

        except Exception as e:
            traceback.print_exc()

        return result

    def process_value(self, value):
        if value is None:
            return 'null'

        if isinstance(value, str):
            value = str(value).replace("'", "''")
            return "'{}'".format(value)

        return value

    def insert(self, sql: str = None, datum: dict = None, table_name=None, target_db=None, replace=False,
               db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        try:
            if sql is not None:
                exec_sql = sql
            elif datum is not None and table_name is not None:
                exec_sql = '''
                {method} into {table_name} ({columns}) values ({values})
                '''.format(
                    method='replace' if replace else 'insert',
                    table_name=table_name,
                    columns=','.join(str(field) for field in datum.keys()),
                    values=','.join(str(self.process_value(val)) for val in datum.values())
                )
            else:
                return None

            result_proxy = self.__get_engine(target_db, db_username, db_passwd, db_host, db_port, db_name).execute(exec_sql)
            return result_proxy.lastrowid

        except Exception as e:
            exception_str = str(e)
            if 'Duplicate entry' in exception_str:
                print("\n** {}\n".format(exception_str))
            else:
                traceback.print_exc()

            result = None

        return result

    def insert_batch(self, data: list, table_name, target_db=None, batch=3000, replace=False,
                     db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        try:
            tpl = '''
            {method} into {table_name} ({columns}) values {values}
            '''

            total = len(data)
            qty = math.ceil(total / batch)
            for i in range(qty):
                begin = batch * i
                end = batch * (i + 1)
                if end > total:
                    end = total
                if begin > end:
                    begin = end

                sub_data = data[begin:end]
                value_parts = []
                for j in range(len(sub_data)):
                    value_parts.append(",".join(str(self.process_value(x)) for x in sub_data[j].values()))

                sql = tpl.format(
                    method='replace' if replace else 'insert', table_name=table_name,
                    columns=','.join(sub_data[0].keys()), values=','.join("({})".format(x) for x in value_parts)
                )

                self.__get_engine(target_db, db_username, db_passwd, db_host, db_port, db_name).execute(sql)
        except Exception as e:
            traceback.print_exc()

    def update(self, update_sql=None, datum: dict = None, table_name=None, where=None, target_db=None,
               db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        try:
            if update_sql is not None:
                sql = update_sql
            elif datum is not None and table_name is not None and where is not None:
                set_params = []
                for field, value in datum.items():
                    set_params.append("{}={}".format(field, self.process_value(value)))
                sql = "update {} set {} where {}".format(table_name, ','.join(set_params), where)
            else:
                return False

            self.__get_engine(target_db, db_username, db_passwd, db_host, db_port, db_name).execute(sql)
            result = True
        except Exception as e:
            traceback.print_exc()
            result = False

        return result

    def delete(self, delete_sql, args=None, target_db=None,
               db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        try:
            self.query(delete_sql, args, target_db=target_db,
                       db_username=db_username, db_passwd=db_passwd, db_host=db_host, db_port=db_port, db_name=db_name)
            result = True
        except Exception as e:
            traceback.print_exc()
            result = False

        return result

    def get_first(self, statement_sql, args=None, default_result=None, target_db=None,
                  db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        result = self.query(statement_sql, args, default_result=default_result, target_db=target_db,
                            db_username=db_username, db_passwd=db_passwd, db_host=db_host, db_port=db_port, db_name=db_name)
        if result is not None and len(result) > 0:
            return result[0]
        return None

    def get_list(self, statement_sql, args=None, default_result=None, target_db=None,
                 db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        result = self.query(statement_sql, args, default_result=default_result, target_db=target_db,
                            db_username=db_username, db_passwd=db_passwd, db_host=db_host, db_port=db_port, db_name=db_name)

        if result is None:
            return []

        _list = []
        for item in result:
            if len(item.keys()) > 1:
                _list.append(item)
            else:
                _list.append(item[list(item.keys())[0]])

        return _list

    def get_value(self, statement_sql, args=None, default_result=None, target_db=None,
                  db_username=None, db_passwd=None, db_host=None, db_port='3306', db_name=None):
        row = self.get_first(statement_sql, args, default_result, target_db=target_db,
                             db_username=db_username, db_passwd=db_passwd, db_host=db_host, db_port=db_port, db_name=db_name)
        if row is None:
            return None

        return list(row.values())[0]

    def build_in_condition(self, prefix, value, separator=','):
        conditions = []
        args = {}

        for sub_val in set(str(value).split(separator)):
            key = "{}{}".format(prefix, sub_val.strip())
            conditions.append(":{}".format(key))
            args[key] = sub_val

        return ",".join(conditions), args
